package com.ruoyi.common.utils.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EasyExcelFillFixedCellExample {

    public static void main(String[] args) throws IOException {
        fileExcel();
    }

    public static void fileExcel() throws IOException {
        String templateEx = "ruoyi-common/src/main/resources/template/excel/P-Well铝注入(1)外包随工单.xlsx";
        String targetEx = "ruoyi-common/src/main/resources/template/out/P-Well铝注入(1)外包随工单3.xlsx";
        File file = new File(templateEx);
        FileInputStream fileInputStream = new FileInputStream(file);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
//        //填充到sheet的数据
        List<FillData> f = getData();
        int fsize = f.size();
        int sheetNum = (int)fsize/4 + (fsize%4==0?0:1);
        //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        //设置模板的第一个sheet的名称
        workbook.setSheetName(0, "第" + 1 + "箱");
        for (int i = 1; i < sheetNum; i++) {
            //复制模板，得到第i个sheet
            int num = i + 1;
            workbook.cloneSheet(0, "第" + num + "箱");
        }
        //写到流里
        workbook.write(bos);
        byte[] bArray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bArray);
        //输出文件路径
        ExcelWriter excelWriter = EasyExcel.write(targetEx).withTemplate(is).build();

        for (int i = 0; i < sheetNum; i++) {
            WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
            List<FillData> f1 = new ArrayList<>();
            int start = 4*i;
            int end = ((i+1) == sheetNum ? (f.size()-1) : (start+3));
            int sum = 0;
            for (int j = start,k=1; j < (end+1) ;j++,k++) {
                FillData fillData = f.get(j);
                fillData.setNo(k);
                sum += fillData.getQty();
                f1.add(fillData);
            }
            excelWriter.fill(f1, writeSheet);

            Map<String, String> map = new HashMap<>();
            map.put("sum", String.valueOf(sum));
            //填充总计
            excelWriter.fill(map, writeSheet);
        }
        // 关闭流
        excelWriter.finish();
    }

    public static void test1() throws IOException {
        //        Path sou22 = Paths.get("ruoyi-common/src/main/resources/template/out/123.xlsx");
//        Path sou212 = Paths.get("ruoyi-common/src/main/resources/template/out/1234.xlsx");
//        Files.createFile(sou22);
//        Files.copy(sou22, sou212, StandardCopyOption.COPY_ATTRIBUTES);

//        Path sou22 = Paths.get("ruoyi-common/src/main/resources/template/excel/P-Well铝注入(1)外包随工单.xlsx");
//        Path sou212 = Paths.get("ruoyi-common/src/main/resources/template/excel/P-Well铝注入(1)外包随工单2.xlsx");
//        Files.copy(sou22, sou212, StandardCopyOption.COPY_ATTRIBUTES);

        ExcelWriter excelWriter = null;
        String sourceEx = "ruoyi-common/src/main/resources/template/excel/P-Well铝注入(1)外包随工单.xlsx";
        String targetEx = "ruoyi-common/src/main/resources/template/excel/P-Well铝注入(1)外包随工单3.xlsx";

        Path sourcePath = Paths.get(sourceEx);
        Path destinationPath = Paths.get(targetEx);
        Files.copy(sourcePath, destinationPath, StandardCopyOption.COPY_ATTRIBUTES);

        // 原始文件路径
        String originalFilePath = "path/to/your/original/file.xlsx";
        // 新文件路径
        String newFilePath = "path/to/your/new/file.xlsx";

        try {
            excelWriter = EasyExcel.write(destinationPath.toFile()).withTemplate(new FileInputStream(sourcePath.toFile())).excelType(ExcelTypeEnum.XLSX).build();
            excelWriter.fill(fillData(), EasyExcel.writerSheet(0).build());
//            excelWriter.fill(fillData(), EasyExcel.writerSheet(1).build());
//            excelWriter.fill(fillData(), EasyExcelFactory.writerSheet(2).build());
//            excelWriter.fill(fillData(), EasyExcelFactory.writerSheet(3).build());
//            excelWriter.fill(fillData(), EasyExcelFactory.writerSheet(4).build());
            excelWriter.finish();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    public static List<FillData> fillData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 2; i++) {
            FillData fillData = new FillData();
            fillData.setNo(1);
            fillData.setPartname("P1008");
            fillData.setLot("GLW313133");
            fillData.setQty(21);
            fillData.setLotnumn("01#-15#");
            list.add(fillData);
        }
        return list;
    }

    public static List<FillData> getData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 1; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setNo(i);
            fillData.setPartname("P1008");
            fillData.setLot("GLW313133");
            fillData.setQty(21);
            fillData.setLotnumn("01#-15#");
            list.add(fillData);
        }
        return list;
    }

}

class FillData {
    private Integer no;
    private String partname;
    private String lot;
    private Integer qty;
    private String lotnumn;

    public Integer getNo() {
        return no;
    }

    public Integer getQty() {
        return qty;
    }

    public String getLot() {
        return lot;
    }

    public String getLotnumn() {
        return lotnumn;
    }

    public String getPartname() {
        return partname;
    }

    public void setLot(String lot) {
        this.lot = lot;
    }

    public void setLotnumn(String lotnumn) {
        this.lotnumn = lotnumn;
    }

    public void setNo(Integer no) {
        this.no = no;
    }

    public void setPartname(String partname) {
        this.partname = partname;
    }

    public void setQty(Integer qty) {
        this.qty = qty;
    }

}